package com.server.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.server.CONFIG;
import com.server.constants.CONSTANTS;
import com.server.constants.CONSTANTS.ACCOUNT;
import com.server.constants.CONSTANTS.PRODUCT;

public class DatabaseManager
{
	private static Connection _connection = null;
	private static Logger logger = Logger.getLogger("DatabaseManager");

	public static void open()
	{
		logger.info("Database opening");
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			logger.info("Database connecting: jdbc:mysql://" + CONFIG.DB_SERVER
					+ "?username=" + CONFIG.DB_USERNAME + "?password="
					+ CONFIG.DB_PASSWORD);

			_connection = DriverManager.getConnection("jdbc:mysql://"
					+ CONFIG.DB_SERVER, CONFIG.DB_USERNAME, CONFIG.DB_PASSWORD);
			logger.info("Database connection established");
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
			logger.info("Mysql driver not found!");
			logger.error(e.getMessage());
		} catch (InstantiationException e)
		{
			e.printStackTrace();
			logger.info("Mysql driver not found!");
			logger.error(e.getMessage());
		} catch (SQLException e)
		{
			e.printStackTrace();
			logger.info("Cannot connect to database server");
			logger.error(e.getMessage());
			_connection = null;
		} catch (IllegalAccessException e)
		{
			e.printStackTrace();
			logger.info("Mysql driver not found!");
			logger.error(e.getMessage());
		} catch (Exception e)
		{
			e.printStackTrace();
			logger.info("Mysql driver not found!");
			logger.error(e.getMessage());
		}
		logger.info("Database opened successful!");
	}

	public static void close()
	{
		if (_connection != null)
		{
			try
			{
				_connection.close();
				logger.info("Database connection terminated");
			} catch (Exception e)
			{
				e.printStackTrace();
				logger.info("Error when Database connection terminating");
				logger.error(e.getMessage());
			}
		}
	}

	/**
	 * create table
	 * 
	 * @param tableName
	 * @param columnSet
	 *            Example: createTable("ABC",
	 *            "id INT UNSIGNED NOT NULL AUTO_INCREMENT," +
	 *            "PRIMARY KEY (id)," + "name CHAR(40), category CHAR(40)");
	 */
	public static boolean createTableIfNotExist(String tableName,
			String columnSet)
	{
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
			return false;
		}
		try
		{
			// Statement s = _connection.createStatement();
			Statement s1 = _connection.createStatement();

			// create new tables
			// String searchTable = "SELECT table_name "
			// + " FROM information_schema.tables "
			// + "WHERE table_schema = '" + tableName + "'";
			//
			// ResultSet resultSet = s.executeQuery(searchTable);
			//
			// if (!resultSet.next())

			// logger.info("DROP TABLE IF EXISTS " + tableName);
			// s.executeUpdate("DROP TABLE IF EXISTS " + tableName);
			// s.close();
			//
			String sql = "CREATE TABLE IF NOT EXISTS " + tableName + "("
					+ columnSet + ")";
			logger.info(sql);
			s1.execute(sql);
			s1.close();

			close();

		} catch (SQLException exception)
		{
			close();
			exception.printStackTrace();
			logger.error(exception.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * @param tableName
	 * @param columnNameSet
	 * @param columnValueSet
	 * 
	 *            Example: insert(animal, "name, category", "'snake', 'reptile'"
	 *            );
	 */

	public static boolean insert(String tableName, String columnNameSet,
			String columnValueSet)
	{
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
			return false;
		}
		try
		{
			Statement s = _connection.createStatement();
			int count;

			logger.info("INSERT INTO " + tableName + " (" + columnNameSet + ")"
					+ " VALUES(" + columnValueSet + ")");

			count = s.executeUpdate("INSERT INTO " + tableName + " ("
					+ columnNameSet + ")" + " VALUES(" + columnValueSet + ")");

			s.close();
			logger.info(count + " rows were inserted");

			close();

		} catch (SQLException e)
		{
			close();
			e.printStackTrace();
			logger.error(e.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * @param tableName
	 * @param columnNameSet
	 * @param columnValueSet
	 * 
	 *            Example: insert(animal, "name, category", "'snake', 'reptile'"
	 *            );
	 */

	public static long insertReturnId(String tableName, String columnNameSet,
			String columnValueSet)
	{
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
			close();
			return -1;
		}

		long id = -1;

		try
		{
			Statement s = _connection.createStatement();

			logger.info("INSERT INTO " + tableName + " (" + columnNameSet + ")"
					+ " VALUES(" + columnValueSet + ")");

			s.executeUpdate("INSERT INTO " + tableName + " (" + columnNameSet
					+ ")" + " VALUES(" + columnValueSet + ")",
					Statement.RETURN_GENERATED_KEYS);

			ResultSet resultSet = s.getGeneratedKeys();
			if (resultSet != null && resultSet.next())
			{
				id = resultSet.getLong(1);
			}

			s.close();
			close();

		} catch (SQLException e)
		{
			close();
			e.printStackTrace();
			logger.error(e.getMessage());
			return -1;
		}
		return id;
	}

	/**
	 * insert with datetime type
	 * 
	 * datetime[] must not null&&length != 1
	 * 
	 * @param tableName
	 * @param columnNameSet
	 * @param columnValueSet
	 * 
	 *            Example: insert(animal, "name, category", "'snake', 'reptile'"
	 *            , datetime[]);
	 */

	public static boolean insert(String tableName, String columnNameSet,
			String columnValueSet, Timestamp[] timestamp)
	{
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
			return false;
		}
		try
		{

			int count;

			String query = "INSERT INTO " + tableName + " (" + columnNameSet
					+ ")" + " VALUES(" + columnValueSet + ")";
			logger.info(query);

			PreparedStatement s = _connection.prepareStatement(query);
			for (int i = 0; i < timestamp.length; i++)
			{
				if (timestamp[i] == null) s.setString(i + 1, "");
				else s.setTimestamp(i + 1, timestamp[i]);
			}

			count = s.executeUpdate();

			s.close();
			logger.info(count + " rows were inserted");

			close();

		} catch (SQLException e)
		{
			close();
			e.printStackTrace();
			logger.error(e.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * insert with datetime type
	 * 
	 * datetime[] must not null&&length != 1
	 * 
	 * @param tableName
	 * @param columnNameSet
	 * @param columnValueSet
	 * 
	 *            Example: insert(animal, "name, category", "'snake', 'reptile'"
	 *            , datetime[]);
	 */

	public static long insertReturnId(String tableName, String columnNameSet,
			String columnValueSet, Timestamp[] timestamp)
	{
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
			close();
			return -1;
		}

		long id = -1;
		try
		{

			String query = "INSERT INTO " + tableName + " (" + columnNameSet
					+ ")" + " VALUES(" + columnValueSet + ")";
			logger.info(query);

			PreparedStatement s = _connection.prepareStatement(query,
					PreparedStatement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < timestamp.length; i++)
			{
				s.setTimestamp(i + 1, timestamp[i]);
			}

			s.executeUpdate();
			ResultSet resultSet = s.getGeneratedKeys();
			if (resultSet != null && resultSet.next())
			{
				id = resultSet.getLong(1);
			}

			s.close();

			close();

		} catch (SQLException e)
		{
			close();
			e.printStackTrace();
			logger.error(e.getMessage());
			return -1;
		}
		return id;
	}

	/**
	 * update with datetime type
	 * 
	 * datetime[] must not null&&length != 1
	 * 
	 * @param tableName
	 * @param columnNameSet
	 * @param columnValueSet
	 * 
	 *            Example: updateReturnId(animal, "name, category",
	 *            "'snake', 'reptile'" , datetime[]);
	 */

	public static long updateReturnId(String tableName, String valueSet,
			Timestamp[] timestamp, String where)
	{
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
			close();
			return -1;
		}

		long id = -1;
		try
		{

			String query = "UPDATE " + tableName + " SET " + valueSet
					+ " WHERE " + where;
			logger.info(query);

			PreparedStatement s = _connection.prepareStatement(query,
					PreparedStatement.RETURN_GENERATED_KEYS);
			if (timestamp != null)
			{
				for (int i = 0; i < timestamp.length; i++)
				{
					s.setTimestamp(i + 1, timestamp[i]);
				}
			}
			s.executeUpdate();
			ResultSet resultSet = s.getGeneratedKeys();
			if (resultSet != null && resultSet.next())
			{
				id = resultSet.getLong(1);
			}

			s.close();

			close();

		} catch (SQLException e)
		{
			close();
			e.printStackTrace();
			logger.error(e.getMessage());
			return -1;
		}
		return id;
	}

	/**
	 * Get all from table Account
	 *            Example: getAllAccounts();
	 */

	public static List<Account> getAllAccounts()
	{
		ResultSet resultSet = null;
		List<Account> accountsList = null; 

		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
		}
		else
		{
			try
			{
				String query = "SELECT * FROM " + ACCOUNT.TABLE_NAME;
				logger.info(query);
				Statement s = _connection.createStatement();

				resultSet = s.executeQuery(query);
				
				if(resultSet!= null)
				{
					accountsList = new ArrayList<Account>();
					
					while (resultSet.next())
					{
						Account account = new Account();
						account.setActive(resultSet.getInt(CONSTANTS.ACCOUNT.COLUMN_ACTIVE) == 1);
						account.setAmount(resultSet.getInt(CONSTANTS.ACCOUNT.COLUMN_AMOUNT));
						account.setCellPhone(resultSet.getString(CONSTANTS.ACCOUNT.COLUMN_USER_NAME).trim());
						
						Date date = resultSet.getDate(CONSTANTS.ACCOUNT.COLUMN_DATE_CREATED);						
						account.setDateCreated(date.getTime());
						
						account.setGroupId(resultSet.getInt(CONSTANTS.ACCOUNT.COLUMN_GROUP_ID));
						account.setPassword(resultSet.getString(CONSTANTS.ACCOUNT.COLUMN_PASSWORD));
						account.setPinCode(resultSet.getString(CONSTANTS.ACCOUNT.COLUMN_PIN_CODE));
						account.setPositionId(resultSet.getInt(CONSTANTS.ACCOUNT.COLUMN_POSITION_ID));
						account.setStaffId(resultSet.getString(CONSTANTS.ACCOUNT.COLUMN_STAFF_ID));
						account.setTerminated(resultSet.getInt(CONSTANTS.ACCOUNT.COLUMN_TERMINATED) == 1);
						account.setUsername(resultSet.getString(CONSTANTS.ACCOUNT.COLUMN_USER_NAME));

						logger.info("get account : " + account.getCellPhone());
						
						accountsList.add(account);
					}
				}

				s.close();

			} catch (SQLException e)
			{
				e.printStackTrace();
				logger.error(e.getMessage());
				accountsList = null;
			}
		}

		close();

		return accountsList;
	}

	/**
	 * Get all from table Product
	 * 
	 * @param tableName
	 * @param columnNameSet
	 * @param columnValueSet
	 * 
	 *            Example: getAllProducts();
	 */

	public static List<Product> getAllProducts()
	{
		ResultSet resultSet = null;
		List<Product> productsList = null; 

		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
		}
		else
		{
			try
			{
				String query = "SELECT * FROM " + PRODUCT.TABLE_NAME;
				logger.info(query);
				Statement s = _connection.createStatement();

				resultSet = s.executeQuery(query);
				
				if(resultSet!= null)
				{
					productsList = new ArrayList<Product>();
					
					while (resultSet.next())
					{
						Product product = new Product();
						product.setCode(resultSet.getString(CONSTANTS.PRODUCT.COLUMN_CODE));
						//product.setDescription(resultSet.getString(CONSTANTS.PRODUCT.COLUMN_PRODUCT_CODE));
						product.setGroupId(resultSet.getInt(CONSTANTS.PRODUCT.COLUMN_GROUP_ID));
						product.setImageName(resultSet.getString(CONSTANTS.PRODUCT.COLUMN_IMAGE_PATH));
						product.setName(resultSet.getString(CONSTANTS.PRODUCT.COLUMN_NAME));
						product.setPriceBuy(resultSet.getLong(CONSTANTS.PRODUCT.COLUMN_PRICE_BUYING));
						product.setPriceSell(resultSet.getLong(CONSTANTS.PRODUCT.COLUMN_PRICE_SELLING));
						productsList.add(product);
					}
				}

				s.close();

			} catch (SQLException e)
			{
				e.printStackTrace();
				logger.error(e.getMessage());
				productsList = null;
			}
		}

		close();

		return productsList;
	}

	/**
	 * Get one record from table
	 * 
	 * @param tableName
	 * @param where
	 * 
	 *            Example: get("animal", "id=1");
	 */

	public static ResultSet get(String tableName, String where)
	{
		ResultSet resultSet = null;
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
		}
		else
		{
			try
			{
				String query = "SELECT * FROM " + tableName + " WHERE " + where;
				logger.info(query);
				Statement s = _connection.createStatement();

				resultSet = s.executeQuery(query);

				s.close();

			} catch (SQLException e)
			{
				e.printStackTrace();
				logger.error(e.getMessage());
			}
		}
		close();

		return resultSet;
	}

	/**
	 * Get total boxs from table VM
	 * 
	 * @param tableName
	 * @param where
	 * 
	 *            Example: get("animal", "id=1");
	 */

	public static int getBoxAmount(String where)
	{
		int totalBox = CONFIG.MAX_BOX_AMOUNT;
		open();

		if (_connection == null)
		{
			logger.info("Connect lost!");
		}
		else
		{
			try
			{
				ResultSet resultSet;

				String query = "SELECT * FROM " + CONSTANTS.MACHINE.TABLE_NAME + " WHERE " + where;
				logger.info(query);
				Statement s = _connection.createStatement();

				resultSet = s.executeQuery(query);

				if (resultSet != null)
				{
					resultSet.next();
					try
					{
						totalBox = resultSet.getInt(CONSTANTS.MACHINE.COLUMN_AMOUNT_BOX);
					} catch (SQLException e1)
					{
						e1.printStackTrace();
					}
					logger.info("Total box is : " + totalBox);

					resultSet.close();
				}

				s.close();

			} catch (SQLException e)
			{
				e.printStackTrace();
				logger.error(e.getMessage());
			}
		}
		close();

		return totalBox;
	}

}
